--USE scm_main;
-- 需要先创建拆分字符串存储过程（split_str）
if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_team_get_data')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_team_get_data
GO
CREATE PROCEDURE bd_proc_team_get_data
	@startDate as varchar(20),--查询开始时间
	@endDate as varchar(20),--查询结束时间
	@deptIds as varchar(max),--选择的部门ids
  @startRow as INT,--分页-开始行数
  @endRow as INT,--分页-结束行数
  @totalSize as INT OUTPUT
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;


	--  异常扑捉机制
		BEGIN TRY


			CREATE TABLE #data(
				id int identity,
				dept_id int,
				card_date date,
				shift_id int,
				swipe_begin int,
				swipe_end int
			)


		-- 开启事务
			BEGIN TRAN



				declare @lstr1 varchar(max)
				declare @lstr2 varchar(max)
				declare @sqlstr varchar(max)
				declare @lstrCount nvarchar(max)
				set @lstr1 = ' ';
				set @lstr2 = ' ';
        set @lstrCount = ' ';
        set @totalSize = 100000

				select @lstr1=@lstr1 + '[' +t.shift_id + '] as '''+t.bh+''',' ,@lstr2=@lstr2 + '[' +t.shift_id + '],' from (select distinct ltrim(str(shift_id)) as shift_id,mc,bh from bd_kq_timecard t INNER JOIN zt_banci z on t.shift_id = z.id where convert(varchar(10),card_date) BETWEEN @startDate and @endDate and dept_id in(select v.value from string_split(@deptIds,',')v) and t.shift_id>0) as t

				if @lstr1 !=' '
				BEGIN

					set @lstr1=left(@lstr1,len(@lstr1)-1)
					set @lstr2=left(@lstr2,len(@lstr2)-1)

					-- 封装数据
					insert into #data(dept_id,shift_id,card_date,swipe_begin,swipe_end)
					select dept_id, shift_id, card_date, swipe_begin, swipe_end
					from bd_kq_timecard a
					inner join string_split(@deptIds,',') b on a.dept_id = b.value
					where isnull(shift_id,0) > 0 and card_date >= @startDate and card_date <= @endDate

					set @sqlstr = '
									select * from (
									    select a.*,b.dep_no_add as teamNo,b.dep_name as teamName,convert(varchar(20),a.card_date,120) as cardDate,case a.in_or_out when 0 then ''进'' when 1 then ''出'' end as inOrOut,row_number() over (order by a.card_date desc,a.dept_id,a.in_or_out) as rowId
                      from (
                        select b.*,c.sumUser
                        from (
                          select p.dept_id, p.card_date, 0 as in_or_out,{1}
                          from (
                            select dept_id, shift_id, card_date, count(swipe_begin) qty
                            from #data
                            where isnull(swipe_begin,0) > 0
                            group by dept_id, shift_id, card_date
                          ) t
                          pivot (
                            sum(qty)
                            for shift_id IN ({2})
                          ) p

                          union all

                          select p.dept_id, p.card_date, 1 as in_or_out,{1}
                          from (
                            select dept_id, shift_id, card_date, count(swipe_end) qty
                            from #data
                            where isnull(swipe_end,0) > 0
                            group by dept_id, shift_id, card_date
                          ) t
                          pivot (
                            sum(qty)
                            for shift_id IN ({2})
                          ) p
                        ) as b
                        inner join (
                          select dept_id,card_date,count(0) as sumUser
                          from (
                            select p.dept_id, p.card_date, 0 as in_or_out,{1}
                            from (
                              select dept_id, shift_id, card_date, count(swipe_begin) qty
                              from #data
                              where isnull(swipe_begin,0) > 0
                              group by dept_id, shift_id, card_date
                            ) t
                            pivot (
                              sum(qty)
                              for shift_id IN ({2})
                            ) p

                            union all

                            select p.dept_id, p.card_date, 1 as in_or_out,{1}
                            from (
                              select dept_id, shift_id, card_date, count(swipe_end) qty
                              from #data
                              where isnull(swipe_end,0) > 0
                              group by dept_id, shift_id, card_date
                            ) t
                            pivot (
                              sum(qty)
                              for shift_id IN ({2})
                            ) p
                          ) as d
                          group by dept_id,card_date
                        ) c on b.dept_id = c.dept_id and b.card_date = c.card_date

                      ) a
                      inner join dt_dep b on a.dept_id = b.dep_serial
									 ) a
									where rowId between  '+STR(@startRow)+' and '+STR(@endRow) +'
									order by a.card_date desc,a.dept_id,a.in_or_out
								';

          set @lstrCount= '
									select @count = count(0)
									from (
										select b.*
										from (
											select p.dept_id, p.card_date, 0 as in_or_out,{1}
											from (
											  select dept_id, shift_id, card_date, count(swipe_begin) qty
											  from #data
											  where isnull(swipe_begin,0) > 0
											  group by dept_id, shift_id, card_date
											) t
											pivot (
												sum(qty)
												for shift_id IN ({2})
											) p

											union all

											select p.dept_id, p.card_date, 1 as in_or_out,{1}
											from (
											  select dept_id, shift_id, card_date, count(swipe_end) qty
											  from #data
											  where isnull(swipe_end,0) > 0
											  group by dept_id, shift_id, card_date
											) t
											pivot (
												sum(qty)
												for shift_id IN ({2})
											) p
										) as b
									) a
									inner join dt_dep b on a.dept_id = b.dep_serial
								';


					set @sqlstr=replace(@sqlstr,'{1}',@lstr1)
					set @sqlstr=replace(@sqlstr,'{2}',@lstr2)
          set @lstrCount=replace(@lstrCount,'{1}',@lstr1)
          set @lstrCount=replace(@lstrCount,'{2}',@lstr2)
          exec sp_executesql @lstrCount, N'@count int output', @totalSize output

					print @lstrCount
					exec (@sqlstr)



				END



		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
		END CATCH


--  清除临时表
	drop table #data;


-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
